const connection = require("../app/database");

class MomentService {
  /**
   * 创建动态
   * @param {string} content 动态内容
   * @param {number} userId 用户id
   */
  async create(content, userId) {
    const statement = "INSERT INTO moment (content, user_id) VALUES (?, ?);";
    const [result] = await connection.execute(statement, [content, userId]);
    return result;
  }
  /**
   * 查询动态列表
   * @param {number} offset 偏移量
   * @param {number} size 查询条数
   */
  async queryList(offset = 0, size = 10) {
    const statement = `
      SELECT 
        m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
        JSON_OBJECT('id', u.id, 'name', u.name, 'avatarURL', u.avatar_url, 'createTime', u.createAt, 'updateTime', u.updateAt) user,
        (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id) commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
      FROM moment m
      LEFT JOIN user u ON u.id = m.user_id
      LIMIT ? OFFSET ?;
    `;
    const [reslut] = await connection.execute(statement, [String(size), String(offset)]);
    return reslut;
  }
  /**
   * 根据id查询动态
   * @param {number} id 动态id
   */
  async queryById(id) {
    const statement = `
    SELECT 
      m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
      JSON_OBJECT('id', u.id, 'name', u.name, 'avatarURL', u.avatar_url, 'createTime', u.createAt, 'updateTime', u.updateAt) user,
      (
        SELECT 
          JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 
            'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarURL', u.avatar_url)
          )) 
        FROM comment c
        LEFT JOIN user cu ON c.user_id = cu.id
        WHERE c.moment_id = m.id
      ) comments,
      (
        JSON_ARRAYAGG(JSON_OBJECT('id', l.id,'name', l.name))
      ) labels
    FROM moment m
    LEFT JOIN user u ON u.id = m.user_id
    LEFT JOIN moment_label ml ON ml.moment_id = m.id
    LEFT JOIN label l ON ml.label_id = l.id
    WHERE m.id = ?
    GROUP BY m.id;
    `;
    const [reslut] = await connection.execute(statement, [id]);
    return reslut;
  }
  /**
   * 更新动态
   * @param {number} id 动态id
   * @param {string} content 动态内容
   */
  async updateById(id, content) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`;
    const [reslut] = await connection.execute(statement, [String(content), id]);
    return reslut;
  }
  /**
   * 删除动态
   * @param {number} id 动态id
   */
  async remove(id){
    const statement = `DELETE FROM moment WHERE id = ?;`;
    const [reslut] = await connection.execute(statement, [id]);
    return reslut;
  }
  /**
   * 判断是否存在momentId对应的labelId
   * @param {number} momentId 动态id
   * @param {number} labelId 标签id
   */
  async hasLabel(momentId, labelId){
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;`;
    const [reslut] = await connection.execute(statement, [momentId, labelId]);
    return Boolean(reslut.length);
  }
  /**
   * 添加标签
   * @param {number} momentId 动态id
   * @param {number} labelId 标签id
   */
  async addLabel(momentId, labelId) {
    const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`;
    const [reslut] = await connection.execute(statement, [momentId, labelId]);
    return reslut;
  }
}

module.exports = new MomentService();
